package com.lagou.test;

import com.lagou.entity.Employee;
import com.lagou.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.*;
import java.util.List;

public class Etest {

    @Test
    public void testFindByName() throws SQLException {

        //1.获取连接
        Connection con = DBUtils.getConnection();

        //2.获取预处理对象
        String sql = "select * from employee where ename = ?";
        PreparedStatement ps = con.prepareStatement(sql);

        //3.设置占位符的值
        ps.setString(1,"貂蝉");
        ResultSet resultSet = ps.executeQuery();

        //4.处理结果集
        while (resultSet.next()){
            int eid = resultSet.getInt("eid");
            String ename = resultSet.getString("ename");
            String gender = resultSet.getString("gender");
            double salary = resultSet.getDouble("salary");
            String bonus = resultSet.getString("bonus");
            Date date = resultSet.getDate("join_DATE");

            System.out.println(eid +" " + ename +" "+ gender +" "+ salary +" "+ bonus +" "+ date);
        }

        //5.释放资源
        DBUtils.close(con,ps,resultSet);
    }

    @Test
    public void testFindByGender() throws SQLException {

        //1.获取连接
        Connection con = DBUtils.getConnection();

        //2.获取预处理对象
        String sql = "select * from employee where gender = ?";
        PreparedStatement ps = con.prepareStatement(sql);

        //3.设置占位符的值
        ps.setString(1,"男");
        ResultSet resultSet = ps.executeQuery();

        //4.处理结果集
        while (resultSet.next()){
            int eid = resultSet.getInt("eid");
            String ename = resultSet.getString("ename");
            String gender = resultSet.getString("gender");
            double salary = resultSet.getDouble("salary");
            String bonus = resultSet.getString("bonus");
            Date date = resultSet.getDate("join_DATE");

            System.out.println(eid +" " + ename +" "+ gender +" "+ salary +" "+ bonus +" "+ date);
        }

        //5.释放资源
        DBUtils.close(con,ps,resultSet);
    }


    @Test
    public void testFindByGender2() throws SQLException {

        QueryRunner qr = new QueryRunner(DBUtils.getDataSource());

        String sql = "select * from employee where gender = ?";

        List<Employee> list = qr.query(sql, new BeanListHandler<Employee>(Employee.class), "男");

        for (Employee employee : list) {
            System.out.println(employee);
        }

    }


}























